
ALTER PROC SP_INSERT_CUSTOMER_PART_NUMBERS

@CUSTOMER_ID INT

AS
BEGIN       

	BEGIN
		DECLARE @PRODUCT_ID INT
		DECLARE @PART_NUMBER_STATUS INT
		DECLARE @CUSTOMER_PART_NUMBER VARCHAR(200)
		
		DECLARE CPNCURSOR CURSOR FOR 			
			SELECT PRODUCT_ID FROM CUSTOMER_PARTNUMBERS WHERE CUSTOMER_ID = @CUSTOMER_ID
		OPEN CPNCURSOR
			FETCH NEXT FROM CPNCURSOR INTO @PRODUCT_ID 
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SELECT @PART_NUMBER_STATUS = COUNT(*) FROM CUSTOMER_PARTNUMBERS WHERE CUSTOMER_ID = @CUSTOMER_ID AND PRODUCT_ID = @PRODUCT_ID AND ODM_ID IS NULL
				IF ( @PART_NUMBER_STATUS = 0 )
				BEGIN
					SELECT @CUSTOMER_PART_NUMBER = CUSTOMER_PARTNUMBER FROM CUSTOMER_PARTNUMBERS WHERE CUSTOMER_ID = @CUSTOMER_ID AND PRODUCT_ID = @PRODUCT_ID
					INSERT INTO CUSTOMER_PARTNUMBERS( CUSTOMER_ID, PRODUCT_ID, CUSTOMER_PARTNUMBER, LABEL_NUMBER ) VALUES ( @CUSTOMER_ID, @PRODUCT_ID , @CUSTOMER_PART_NUMBER,'' )
				END
				SET @PRODUCT_ID = NULL
				FETCH NEXT FROM CPNCURSOR INTO @PRODUCT_ID 
				
			END 
		CLOSE CPNCURSOR
		DEALLOCATE CPNCURSOR
	END
 
END



